# -*- coding: utf-8 -*-
# This code written by ChanGOGOGO
"""
话单月结(客户侧)
"""
import pymysql
from util import (
    logging,
    date_slice,
    get_month_time_scale,
    write_excel,
    query_es,
    time,
    ms2min,
    data2beautify,
    db_ip,
    db_port,
    db_username,
    db_password,
    excel_save_dir,
)


class ExportMonthlyBillsByClient:
    def __init__(self, ui_data):
        self.data_date = ui_data["data_date"]
        self.business = ui_data.get("business")
        self.data_type = ui_data.get("data_type")
        self.call_type = ui_data.get("call_type")
        company_id = ui_data.get("company_id")
        self.db_name = f"yicall_{company_id}"
        self.table_index = ui_data.get("business_id")
        self.conn = pymysql.connect(
            host=db_ip,
            port=db_port,
            user=db_username,
            passwd=db_password,
            charset="utf8",
        )
        self.cursor = self.conn.cursor()
        self.scene_info = self.query_scene()
        self.task_info = self.query_task()
        self.close_db()
        if self.call_type == "呼出":
            self.es_index = f"{self.db_name}_{self.table_index}_callout_session_ach_{self.data_date}"
        else:
            self.es_index = f"{self.db_name}_{self.table_index}_callout_session_in_ach_{self.data_date}"

    def query_scene(self):
        """查询场景信息"""
        sql = f"""select scene_id,scene_name from {self.db_name}.b_{self.table_index}_call_scene
        order by scene_id desc;"""
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        scene_info = {}
        if len(result) > 0:
            scene_info = {scene_id: scene_name for scene_id, scene_name in result}
        return scene_info

    def query_task(self):
        """查询任务信息"""
        if self.call_type == "呼出":
            sql = f"""select task_id,task_name from {self.db_name}.b_{self.table_index}_callout_task;"""
        else:
            sql = f"""select task_id,task_name from {self.db_name}.b_{self.table_index}_callout_task_in;"""
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        task_info = {}
        if len(result) > 0:
            task_info = {task_id: task_name for task_id, task_name in result}
        return task_info

    def close_db(self):
        self.cursor.close()
        self.conn.close()

    def query_es_data(self, day_start_time, day_end_time):
        """按天查询es数据"""
        body = {
            "query": {
                "bool": {
                    "must": [
                        {"term": {"calloutConnectStatus": 100}},
                        {
                            "range": {
                                "calloutFinishTime": {
                                    "gte": day_start_time,
                                    "lte": day_end_time,
                                }
                            }
                        },
                    ]
                }
            }
        }
        fields = [
            "taskId",
            "sceneId",
            "calloutDialNumber",
            "dialogDuration",
            "calloutDialTime",
        ]
        day_es_data = query_es(self.es_index, body, fields)
        return day_es_data

    def export(self):
        """导出通话时长月结账单数据"""
        bills = dict()  # 总账单信息
        # 以“天”为单位查询es数据，避免scroll超时
        query_es_start_time = time.time()
        month_start_time, month_end_time = get_month_time_scale(self.data_date)
        date_slice_result = date_slice(month_start_time, month_end_time)
        handle_data_start_time = time.time()
        for everyday in date_slice_result:
            day_start_time = everyday[0]
            day_end_time = everyday[1]
            es_data = self.query_es_data(day_start_time, day_end_time)
            # ------------------------将数据库查询出来的数据进行归类---------------------
            try:
                for data in es_data:
                    task_id = data.get("taskId")
                    task_name = self.task_info.get(int(task_id))
                    scene_id = data.get("sceneId")
                    scene_name = self.scene_info.get(int(scene_id))
                    line_number = data.get("calloutDialNumber")
                    dialog_duration = data.get("dialogDuration")
                    callout_dial_date = data.get("calloutDialTime")[:10]
                    dialog_duration_min = ms2min(
                        int(dialog_duration)
                    )  # 通话时长由毫秒转换成分钟，向上取整
                    find_scene_result = bills.get(scene_name, -1)
                    if find_scene_result != -1:  # 场景已经存在
                        find_task_result = bills.get(scene_name).get(task_name, -1)
                        if find_task_result != -1:  # 任务已存在
                            find_call_date_result = (
                                bills.get(scene_name)
                                .get(task_name)
                                .get(callout_dial_date, -1)
                            )
                            if find_call_date_result != -1:
                                find_callout_number = (
                                    bills.get(scene_name)
                                    .get(task_name)
                                    .get(callout_dial_date)
                                    .get(line_number, -1)
                                )
                                if find_callout_number != -1:
                                    bills[scene_name][task_name][callout_dial_date][
                                        line_number
                                    ] += dialog_duration_min
                                else:
                                    bills[scene_name][task_name][callout_dial_date][
                                        line_number
                                    ] = dialog_duration_min
                            else:
                                bills[scene_name][task_name][callout_dial_date] = {
                                    line_number: dialog_duration_min
                                }
                        else:
                            bills[scene_name][task_name] = {
                                callout_dial_date: {line_number: dialog_duration_min}
                            }
                    else:
                        bills[scene_name] = {
                            task_name: {
                                callout_dial_date: {line_number: dialog_duration_min}
                            }
                        }
            except Exception as reason:
                logging.error(reason)
        handle_data_end_time = time.time()
        handle_data_take_time = handle_data_end_time - handle_data_start_time
        logging.info(
            f"处理从ElasticSearch中查询出来的数据共计耗时{handle_data_take_time:.2f}秒。"
        )
        logging.info(f"处理完成的数据：\n{data2beautify(bills)}")
        if bool(bills) is False:
            msg = (
                f"<font color=green>无法导出“{self.business}”的【{self.data_type}】，"
                f"原因：\n在{self.data_date}没有{self.call_type}数据！</font>"
            )
            logging.info(msg)
            return {"code": 1, "excel_names": [], "msg": msg}
        else:
            excel_names = list()
            if self.call_type == "呼出":
                excel_title = [
                    "任务名",
                    "所属场景",
                    "主叫号码",
                    "拨打日期",
                    "通话时长(分钟)",
                ]
            else:
                excel_title = [
                    "任务名",
                    "所属场景",
                    "被叫号码",
                    "拨打日期",
                    "通话时长(分钟)",
                ]
            excel_data = list()
            for b_scene_name, task_call_infos in bills.items():
                for b_task_name, everyday_call_infos in task_call_infos.items():
                    for b_call_date, call_num_infos in everyday_call_infos.items():
                        for call_num, b_dialog_duration in call_num_infos.items():
                            excel_data.append(
                                [
                                    b_task_name,
                                    b_scene_name,
                                    call_num,
                                    b_call_date,
                                    b_dialog_duration,
                                ]
                            )
            excel_data.append(
                ["总计", "", "", "", f"=SUM(E2:E{len(excel_data) + 1})"]
            )  # 拨打时长汇总
            sheet_info = [[self.data_type, excel_title, excel_data]]
            file_name = (
                f"{self.business}_({self.call_type}){self.data_type}_{self.data_date}"
            )
            write_excel_start_time = time.time()
            excel_name = write_excel(sheet_info, excel_save_dir, file_name, True)
            excel_names.append(excel_name)
            write_excel_end_time = time.time()
            write_excel_take_time = write_excel_end_time - write_excel_start_time
            logging.info(
                f"将{self.business}的{self.data_type}写入excel共计耗时{write_excel_take_time:.2f}秒。"
            )
            return {"code": 0, "excel_names": excel_names, "msg": None}


if __name__ == "__main__":
    ...
